﻿using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using ChinaDynastyTours.Models;
using System.Text;
using System.Collections.Generic;
using System;

namespace ChinaDynastyTours.Controls
{
    class ExpensesCtrl
    {
        #region 获取相关费用信息

        /// <summary>
        /// 检测指定供应商是否已经有了预订信息(通过费用表)
        /// </summary>
        /// <param name="providerId"></param>
        /// <returns>true存在预订信息，false不存在预订信息</returns>
        public static bool CheckExistOrderInfoForProvider(string providerId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;

            dbCommand = db.GetSqlStringCommand(string.Format(@"SELECT ProviderID FROM fee_Expense WHERE ProviderID='{0}'", providerId.Trim()));

            object obj = db.ExecuteScalar(dbCommand);
            
            return obj!=null;
        }
        /// <summary>
        /// 为月结提供的费用数据查询
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <param name="providerId"></param>
        /// <param name="startTime"></param>
        /// <param name="endTime"></param>
        /// <returns></returns>
        //internal static DataTable GetExpenseData(string tourGroupId, string providerId, DateTime startTime, DateTime endTime)
        //{
        //    Database db = DatabaseFactory.CreateDatabase();
        //    DbCommand dbCommand;


        //    string szCondition = string.Format("WHERE  t.TourgroupID=e.TourgroupID  AND (t.StartDate>'{0}' and t.StartDate<'{1}')", startTime.ToString("yyyy-MM-dd"), endTime.ToString("yyyy-MM-dd"));

        //    if (tourGroupId != "")
        //        szCondition = " AND TourGroupID='" + tourGroupId + "'";
        //    if (szCondition != "")
        //    {
        //        if (providerId != "")
        //            szCondition += " AND ProviderID=" + providerId;
        //    }
        //    szCondition += string.Format(" AND PayStatus='{0}'", PayApplyStatusEnum.月结);

        //    string Sql = @"SELECT [ExpenseID], e.[TourgroupID],e.[TourgroupName], [ProviderID], [ProviderName], [ServiceName], [Budget], [PayTotal], [ApplyPayTotal], [AdjustAmount], [Status], [TypeBelong], e.[Remark],[ApplyPay],[PayStatus],[CreateByUserName],[TourGroupScheduleID] FROM fee_Expense e ,orgt_TourGroup t ";

        //    Sql += szCondition;

        //    dbCommand = db.GetSqlStringCommand(Sql);
        //    DataSet ds = db.ExecuteDataSet(dbCommand);
        //    if (ds.Tables.Count > 0)
        //        return ds.Tables[0];
        //    else
        //        return null;
        //} 

        /// <summary>
        /// 根据团号和供应商ID获取费用信息，用于付款申请数据加载
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <param name="providerId"></param>
        /// <returns></returns>
        internal static DataTable GetExpenseDataforApply(string tourGroupId, string providerId, ProviderTypeEnum[] types, DateTime startDate, DateTime endDate,string payStatus)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;

            string szCondition = "";
            //所属类型条件
            StringBuilder typeSql = new StringBuilder();
            if (types != null)
            {
                for (int i = 0; i < types.Length; i++)
                {
                    if (i == 0)
                        typeSql.AppendFormat(" (TypeBelong='{0}'", types[0]);
                    else
                    {
                        typeSql.AppendFormat(" TypeBelong='{0}'", types[i]);
                    }
                    if (i != types.Length - 1)
                        typeSql.Append(" OR ");
                    else
                        typeSql.Append(")");
                }
            }

            if (tourGroupId != "")
                szCondition = "t.TourGroupID='" + tourGroupId + "'";

            if (szCondition != "")
            {
                if (providerId != "")
                    szCondition += " AND ProviderID='" + providerId + "'";
            }
            else
            {
                if (providerId != "")
                    szCondition += " ProviderID='" + providerId + "'";
            }

            if (szCondition != "")
            {
                if (typeSql.Length != 0)
                    szCondition += " AND " + typeSql.ToString();
            }
            else
            {
                if (typeSql.Length != 0)
                    szCondition += typeSql.ToString();
            }

            if (szCondition != "")
            {
                szCondition += string.Format(" AND (StartDate>='{0}' AND StartDate<='{1}') ", startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"));
            }
            else
            {
                szCondition += string.Format(" (StartDate>='{0}' AND StartDate<='{1}') ", startDate.ToString("yyyy-MM-dd"), endDate.ToString("yyyy-MM-dd"));
            }

            if (szCondition != "")
            {
                if (payStatus != "" && payStatus != "全部")
                    szCondition += " AND PayStatus='" + payStatus + "'";
            }
            else
            {
                if (payStatus != "" && payStatus != "全部")
                    szCondition += " PayStatus='" + payStatus + "'";
            }

            string Sql = @"SELECT [ExpenseID], e.[TourgroupID],t.[TourgroupName], [ProviderID], [ProviderName], [ServiceName], [Budget], [PayTotal], [ApplyPayTotal], [AdjustAmount], [Status], [TypeBelong],e.[Remark],[Budget]-[ApplyPayTotal] as [ApplyPay],[PayStatus],[CreateByUserName],[TourGroupScheduleID] FROM fee_Expense e,orgt_TourGroup t WHERE e.TourgroupID = t.TourgroupID";

            if (szCondition != "")
                Sql += " AND " + szCondition + "AND Status<> '" + ExpenseStatusEnum.取消 + "'";


            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }

        /// <summary>
        /// 根据团号获取费用信息
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <returns></returns>
        internal static DataTable GetExpenseData(string tourGroupId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;

            string szCondition = "";

            if (tourGroupId != "")
                szCondition = "t.TourGroupID='" + tourGroupId + "'";


            string Sql = @"SELECT [ExpenseID], e.[TourgroupID],t.[TourgroupName], [ProviderID], [ProviderName], [ServiceName], [Budget], [PayTotal], [ApplyPayTotal], [AdjustAmount], [Status], [TypeBelong],e.[Remark],[Budget]-[ApplyPayTotal] as [ApplyPay],[PayStatus],[CreateByUserName],[TourGroupScheduleID] FROM fee_Expense e,orgt_TourGroup t WHERE e.TourgroupID = t.TourgroupID";

            if (szCondition != "")
                Sql += " AND " + szCondition + "AND Status<> '" + ExpenseStatusEnum.取消+"'";


            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 根据团号和创建人获取费用信息，用于现金报销数据加载
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <param name="providerId"></param>
        /// <returns></returns>
        internal static DataTable GetExpenseDataForCashAccount(string tourGroupId, string createByUserName)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;

            string szCondition = string.Format("WipeOffMethod='{0}' ", WipeOffMethodEnum.现金报销);

            if (tourGroupId != "")
                szCondition += " AND TourGroupID='" + tourGroupId + "'";
            if (szCondition != "")
            {
                if (createByUserName != "")
                    szCondition += " AND CreateByUserName='" + createByUserName + "'";
            }
            else
            {
                if (createByUserName != "")
                    szCondition += " CreateByUserName='" + createByUserName + "'";
            }

            string Sql = @"SELECT [ExpenseID], [TourgroupID],[TourgroupName], [ProviderID], [ProviderName], [ServiceName], [Budget], [PayTotal], [ApplyPayTotal], [AdjustAmount], [Status], [TypeBelong], [Remark],[ApplyPay],[PayStatus],[CreateByUserName],[TourGroupScheduleID],[ExpenseType] FROM fee_Expense";

            if (szCondition != "")
                Sql += " WHERE " + szCondition;


            dbCommand = db.GetSqlStringCommand(Sql);

            DataSet ds = db.ExecuteDataSet(dbCommand);
            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
 
        /// <summary>
        /// 获取费用信息
        /// </summary>
        /// <param name="tourGroupId">团号</param>
        /// <param name="type">所属类型</param>
        /// <returns></returns>
        internal static DataTable GetExpenseDataByScheduleID(int tourGroupScheduleId, ProviderTypeEnum[] types)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;

            StringBuilder formatSql = new StringBuilder();
            formatSql.AppendFormat(@"SELECT  [ExpenseID], [TourgroupID],[TourgroupName],[ProviderID], [ProviderName],[ServiceName],
                                                    [Budget],[PayTotal],[ApplyPayTotal],[AdjustAmount],[Status],[TypeBelong],[ExpenseTypeID],[ExpenseType],[ApplyPay],[PayStatus] ,[CreateByUserName],[TourGroupScheduleID] 
                                                    FROM fee_Expense
                                                    WHERE  TourGroupScheduleId={0} AND (", tourGroupScheduleId);
            //拼接第1段Select语句
            for (int i = 0; i < types.Length; i++)
            {
                formatSql.AppendFormat(" TypeBelong='{0}'", types[i]);

                if (i != types.Length - 1)
                    formatSql.Append(" OR ");
                else
                    formatSql.Append(")");
            }

            dbCommand = db.GetSqlStringCommand(formatSql.ToString());

            DataSet ds = db.ExecuteDataSet(dbCommand);

            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;

        }
        /// <summary>
        /// 根据团号获取团中所有费用的信息
        /// </summary>
        /// <param name="tourGroupId">团号</param>
        /// <returns></returns>
        internal static DataTable GetExpenseDataByTourgroupID(string tourgroupID, ProviderTypeEnum[] types)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;

            StringBuilder formatSql = new StringBuilder();
            formatSql.AppendFormat(@"SELECT  [ExpenseID], [TourgroupID],[TourgroupName],[ProviderID], [ProviderName],[ServiceName],
                                                    [Budget],[PayTotal],[ApplyPayTotal],[AdjustAmount],[Status],[TypeBelong],[ExpenseTypeID],[ExpenseType],[ApplyPay],[PayStatus] ,[CreateByUserName],[TourGroupScheduleID] 
                                                    FROM fee_Expense
                                                    WHERE  TourgroupID='{0}' AND (", tourgroupID);
            //拼接第1段Select语句
            for (int i = 0; i < types.Length; i++)
            {
                formatSql.AppendFormat(" TypeBelong='{0}'", types[i]);

                if (i != types.Length - 1)
                    formatSql.Append(" OR ");
                else
                    formatSql.Append(")");
            }

            dbCommand = db.GetSqlStringCommand(formatSql.ToString());

            DataSet ds = db.ExecuteDataSet(dbCommand);

            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;

        }
        /// <summary>
        /// 根据费用ID获取费用信息
        /// </summary>
        /// <param name="expenseId">费用ID</param>
        /// <returns></returns>
        internal static DataTable GetExpenseDatabyID(int expenseId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;

            StringBuilder formatSql = new StringBuilder();
            formatSql.AppendFormat(@"SELECT  * FROM fee_Expense WHERE  ExpenseID={0}", expenseId);

            dbCommand = db.GetSqlStringCommand(formatSql.ToString());

            DataSet ds = db.ExecuteDataSet(dbCommand);

            if (ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;

        }

        /// <summary>
        /// 获取团‘预算价’总和
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <returns></returns>
        internal static decimal GetExpenseBudgetTotal(string tourGroupId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string Sql = string.Format(@"SELECT sum([Budget]) as BudgetTotal FROM fee_Expense WHERE TourgroupID='{0}' AND Status<>'{1}'", tourGroupId,ExpenseStatusEnum.取消);

            dbCommand = db.GetSqlStringCommand(Sql);

            string retValue = db.ExecuteScalar(dbCommand).ToString();
            if (!string.IsNullOrEmpty(retValue))
                return decimal.Parse(retValue);
            else
                return 0;
        }
        /// <summary>
        /// 获取团‘已申请总额’和
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <returns></returns>
        internal static decimal GetExpenseApplyPayTotal(string tourGroupId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string Sql = string.Format(@"SELECT sum([ApplyPayTotal]) as ApplyPayTotal FROM fee_Expense WHERE TourgroupID='{0}'and Status<>'{1}'", tourGroupId, ExpenseStatusEnum.取消);

            dbCommand = db.GetSqlStringCommand(Sql);

            string retValue = db.ExecuteScalar(dbCommand).ToString();
            if (!string.IsNullOrEmpty(retValue))
                return decimal.Parse(retValue);
            else
                return 0;
        }
        /// <summary>
        /// 获取团‘调整额’总和
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <returns></returns>
        internal static decimal GetExpenseAdjustAmountTotal(string tourGroupId)
        {

            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string Sql = string.Format(@"SELECT sum([AdjustAmount]) as AdjustAmountTotal FROM fee_Expense WHERE TourgroupID='{0}'", tourGroupId);

            dbCommand = db.GetSqlStringCommand(Sql);

            string retValue = db.ExecuteScalar(dbCommand).ToString();
            if (!string.IsNullOrEmpty(retValue))
                return decimal.Parse(retValue);
            else
                return 0;
        }
        /// <summary>
        /// 获取所有的费用状态，看是否存在‘计划’或‘导游报销’的费用项数据
        /// </summary>
        /// <param name="tourGroupId">团号</param>
        /// <returns>true存在，false不存在</returns>
        internal static bool GetAllExpenseStatusForPendingAudit(string tourGroupId)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string Sql = string.Format(@"SELECT COUNT(*) FROM fee_Expense WHERE TourgroupID='{0}' AND (Status = '{1}' OR Status = '{2}')", tourGroupId, ExpenseStatusEnum.导游报销,ExpenseStatusEnum.计划);

            dbCommand = db.GetSqlStringCommand(Sql);

            object retValue = db.ExecuteScalar(dbCommand);
            return retValue.ToString()!="0";
        }
        /// <summary>
        /// 获取费用状态，看是否存在‘计划’的对账单费用项数据
        /// </summary>
        /// <param name="tourGroupId">团号</param>
        /// <returns>true存在，false不存在</returns>
        internal static bool GetAllExpenseStatusForBill(string tourGroupId, string loginProviderID)
        {
            Database db = DatabaseFactory.CreateDatabase();

            DbCommand dbCommand;
            string Sql = string.Format(@"SELECT TOP 1  ExpenseID FROM fee_Expense WHERE TourgroupID='{0}' AND ProviderID={1} AND Status = '{2}'", tourGroupId, loginProviderID,ExpenseStatusEnum.计划);

            dbCommand = db.GetSqlStringCommand(Sql);

            object retValue = db.ExecuteScalar(dbCommand);
            return retValue == null ? false : true;
        }
        /// <summary>
        /// 获取费用ID，用于对账单
        /// </summary>
        /// <param name="tourgroupId"></param>
        /// <param name="loginProviderID"></param>
        /// <returns></returns>
        internal static string GetExpenseIDForBill(string tourgroupId, string loginProviderID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;
            string Sql;

            Sql = string.Format(@"SELECT expenseid FROM fee_Expense WHERE TourgroupID='{0}' AND ProviderID={1}", tourgroupId, loginProviderID);
            dbCommand = db.GetSqlStringCommand(Sql);
            object obj = db.ExecuteScalar(dbCommand);

            if (obj != null)
                return obj.ToString();
            else
                return "";
        }
        /// <summary>
        /// 获取费用状态，用于对账单
        /// </summary>
        /// <param name="tourgroupId"></param>
        /// <param name="loginProviderID"></param>
        /// <returns></returns>
        internal static string GetExpenseStatusForBill(string tourgroupId, string loginProviderID)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand;
            string Sql;

            Sql = string.Format(@"SELECT status FROM fee_Expense WHERE TourgroupID='{0}' AND ProviderID={1}", tourgroupId, loginProviderID);
            dbCommand = db.GetSqlStringCommand(Sql);
            object obj = db.ExecuteScalar(dbCommand);

            if (obj != null)
                return obj.ToString();
            else
                return "";
        }       
        #endregion

        #region 费用增、删、改


        /// <summary>
        /// 增加费用
        /// </summary>
        /// <param name="expenseEntity"></param>
        /// <returns>返回ExpenseID</returns>
        internal static int CreateExpenseData(ExpenseEntity expenseEntity)
        {
            Database db = DatabaseFactory.CreateDatabase();

            string sqlExpense = string.Format(@"INSERT INTO fee_Expense(
                                        [TourgroupID],
                                        [ProviderID],
                                        [ProviderName],
                                        [ServiceName],
                                        [Budget],
                                        [PayTotal],
                                        [ApplyPayTotal],
                                        [AdjustAmount],
                                        [Status],
                                        [TypeBelong],
                                        [ApplyPay],
                                        [PayStatus],[TourgroupName],[CreateByUserName],[TourGroupScheduleID],[Remark],[CreateTime],[WipeOffMethod],[ExpenseTypeID],[ExpenseType])
                                        VALUES('{0}', {1}, '{2}', '{3}',{4},{5}, {6},{7}, '{8}', '{9}',{10},'{11}','{12}','{13}',{14},'{15}','{16}','{17}',{18},'{19}');Select SCOPE_IDENTITY();",
                                        expenseEntity.TourgroupID,
                                        expenseEntity.ProviderID,
                                        expenseEntity.ProviderName,
                                        expenseEntity.ServiceName,
                                        expenseEntity.Budget,
                                        expenseEntity.PayTotal,
                                        expenseEntity.ApplyPayTotal,
                                        expenseEntity.AdjustAmount,
                                        expenseEntity.Status,
                                        expenseEntity.TypeBelong,
                                        expenseEntity.ApplyPay,
                                        expenseEntity.PayStatus,
                                        expenseEntity.TourgroupName,
                                        expenseEntity.CreateByUserName,
                                        expenseEntity.TourGroupScheduleID,
                                        expenseEntity.Remark,
                                        expenseEntity.CreateTime,
                                        expenseEntity.WipeOffMethod,
                                        expenseEntity.ExpenseTypeID,
                                        expenseEntity.ExpenseType
                                        );
            string expenseID = db.ExecuteScalar(CommandType.Text, sqlExpense).ToString();
            return int.Parse(expenseID);
            //return db.ExecuteNonQuery(CommandType.Text, sqlExpense);
        }

        public static int DeleteExpense(string expenseId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"DELETE FROM fee_expense WHERE  expenseid={0};",
                                              expenseId);//一般来说，此处删除的是导游添加项和‘费用管理’添加项，这些项没有对应的预订，非自订的宾馆也可删除费用
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        /// <summary>
        /// 删除指定费用编号的
        /// </summary>
        /// <param name="expenseId"></param>
        /// <returns></returns>
        public static int DeleteExpense(int expenseId, ProviderTypeEnum type)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql="";
            switch (type)
            {
                case ProviderTypeEnum.宾馆:
                    sql = string.Format(@"DELETE FROM fee_HotelOrder WHERE expenseid={0};
                                         DELETE FROM fee_expense WHERE  expenseid={1};",//同时删除费用和宾馆相关内容
                                                 expenseId, expenseId);
                    break;
                case ProviderTypeEnum.旅行社:
                    sql = string.Format(@"DELETE FROM fee_TravelOrder WHERE expenseid={0};
                                         DELETE FROM fee_expense WHERE  expenseid={1};",//同时删除费用和宾馆相关内容
                                         expenseId, expenseId);
                    break;
                case ProviderTypeEnum.飞机票:
                    break;
                case ProviderTypeEnum.火车票:
                    break;
                case ProviderTypeEnum.汽车:
                    break;
                case ProviderTypeEnum.景点:
                    break;
                case ProviderTypeEnum.演出:
                    break;
                case ProviderTypeEnum.餐厅:
                    break;
                case ProviderTypeEnum.其它:
                    sql = string.Format(@"DELETE FROM fee_expense WHERE  expenseid={0};",expenseId);
                    break;
                default:
                    break;
            }
            return db.ExecuteNonQuery(CommandType.Text, sql);
        }

        /// <summary>
        /// 导游报账，更改娱乐、餐单的“导游报销状态”
        /// </summary>
        /// <param name="trainOrderId">订单号</param>
        /// <param name="orderState">状态</param>
        /// <returns></returns>
        internal static int UpdateExpenseDataWipeOffMethod(string expenseId, WipeOffMethodEnum wipeoffMethod)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sql = string.Format(@"UPDATE fee_Expense SET [WipeOffMethod]='{0}' WHERE ExpenseID={1}", wipeoffMethod, expenseId);

            return db.ExecuteNonQuery(CommandType.Text, sql);
        }
        /// <summary>
        /// 导游报账专用费用数据更新
        /// </summary>
        /// <param name="expenseEntity"></param>
        /// <returns></returns>
        internal static int UpdateExpenseDataForGuideAccount(ExpenseEntity expenseEntity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sqlExpense = string.Format(@"UPDATE fee_Expense SET 
                                        [ServiceName]='{0}',
                                        [Budget]={1},
                                        [ApplyPay]={2},
                                        [ExpenseTypeID]={3},
                                        [ExpenseType]='{4}',
                                        [Remark]='{5}'
                                        WHERE ExpenseID={6}",
                                       expenseEntity.ServiceName,
                                       expenseEntity.Budget,
                                       expenseEntity.ApplyPay,
                                       expenseEntity.ExpenseTypeID,
                                       expenseEntity.ExpenseType,
                                       expenseEntity.Remark,
                                       expenseEntity.ExpenseID);
            return db.ExecuteNonQuery(CommandType.Text, sqlExpense);
        }
        
        /// <summary>
        /// 根据指定的费用标识号，更新除‘已支付总额’、‘已申请总额’、‘本次申请额’、‘调整额’外的全部信息
        /// </summary>
        /// <param name="expenseEntity"></param>
        /// <returns></returns>
        internal static int UpdateExpenseData(ExpenseEntity expenseEntity)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sqlExpense = string.Format(@"UPDATE fee_Expense SET 
                                        [ProviderID]={0},
                                        [ProviderName]='{1}',
                                        [ServiceName]='{2}',
                                        [Budget]={3},
                                        [ExpenseTypeID]='{4}',
                                        [ExpenseType]='{5}'
                                        WHERE ExpenseID={6}",
                                       expenseEntity.ProviderID,
                                       expenseEntity.ProviderName,
                                       expenseEntity.ServiceName,
                                       expenseEntity.Budget,
                                       expenseEntity.ExpenseTypeID,
                                       expenseEntity.ExpenseType,
                                       expenseEntity.ExpenseID);
            return db.ExecuteNonQuery(CommandType.Text, sqlExpense);
        }
        /// <summary>
        /// 根据指定的费用标识号，更新‘调整额’
        /// </summary>
        /// <param name="adjustAmount">调整额额度</param>
        /// <param name="expenseId"></param>
        /// <returns></returns>
        internal static int UpdateExpenseAdjustAmountData(decimal adjustAmount,string expenseId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sqlExpense = string.Format(@"UPDATE fee_Expense SET [AdjustAmount]={0} WHERE ExpenseID={1}",adjustAmount,expenseId);
            return db.ExecuteNonQuery(CommandType.Text, sqlExpense);
        }

        /// <summary>
        /// 根据指定的费用标识号，更新‘服务项名称’（补充摘要）
        /// </summary>
        /// <param name="serviceName">服务项名称</param>
        /// <param name="expenseId"></param>
        /// <returns></returns>
        internal static int UpdateExpenseServiceName(string serviceName, string expenseId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sqlExpense = string.Format(@"UPDATE fee_Expense SET [ServiceName]='{0}' WHERE ExpenseID={1}", serviceName, expenseId);
            return db.ExecuteNonQuery(CommandType.Text, sqlExpense);
        }
        /// <summary>
        /// 根据指定的费用标识号，更新‘已申请总额’
        /// </summary>
        /// <param name="ExpenseID">费用ID</param>
        /// <returns></returns>
        internal static int UpdateExpenseApplyPayTotal(int expenseId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            StringBuilder sql = new StringBuilder();

            sql.AppendFormat(@"UPDATE fee_Expense SET [ApplyPayTotal]=(SELECT SUM(ApplyPay) FROM fin_PayDetail WHERE ExpenseID={0}) WHERE ExpenseID={0}; ", expenseId);
            return db.ExecuteNonQuery(CommandType.Text, sql.ToString());
        }

        /// <summary>
        /// 根据指定的费用标识号，更新‘已支付总额’
        /// </summary>
        /// <param name="ExpenseID">费用ID</param>
        /// <returns></returns>
        internal static int UpdateExpensePayTotal(int expenseId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            StringBuilder sql = new StringBuilder();

            sql.AppendFormat(@"UPDATE fee_Expense SET [PayTotal]=(SELECT SUM(ApplyPay) FROM fin_PayDetail WHERE ExpenseID={0} and PayStatus='{1}') WHERE ExpenseID={0}; ", expenseId, PayDetailStatusEnum.已付);
            return db.ExecuteNonQuery(CommandType.Text, sql.ToString());
        }

        /// 根据指定的费用标识号，更新报销‘已申请总额’
        /// </summary>
        /// <param name="ExpenseID">费用ID</param>
        /// <returns></returns>
        internal static int UpdateExpenseApplyPayTotalCash(int expenseId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            StringBuilder sql = new StringBuilder();

            sql.AppendFormat(@"UPDATE fee_Expense SET [ApplyPayTotal]=(SELECT SUM(ApplyPay) FROM fin_CashDetail WHERE ExpenseID={0}) WHERE ExpenseID={0}; ", expenseId);
            return db.ExecuteNonQuery(CommandType.Text, sql.ToString());
        }

        /// <summary>
        /// 根据指定的费用标识号，更新‘已申请总额’和‘已支付总额’，用于财务终结处的财务调整（新增付款）
        /// </summary>
        /// <param name="applyPayTotal">已申请总额</param>
        /// <param name="expenseId"></param>
        /// <returns></returns>
        internal static int UpdateApplyPayTotal(int expenseId,decimal payApply)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sqlExpense = string.Format(@"UPDATE fee_Expense SET [ApplyPayTotal]=[ApplyPayTotal]+{0},[PayTotal]=[PayTotal]+{0} WHERE ExpenseID={1}", payApply, expenseId);
            return db.ExecuteNonQuery(CommandType.Text, sqlExpense);
        }

        /// <summary>
        /// 更新费用的‘审核状态’信息，‘计划、待审核、审核、导游报销、终结’
        /// </summary>
        /// <param name="expenseId">费用标识</param>
        /// <param name="status">被更新成为状态</param>
        /// <returns></returns>
        internal static int UpdateExpenseStatusData(string expenseId,ExpenseStatusEnum status)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sqlExpense = string.Format(@"UPDATE fee_Expense SET [Status]='{0}' WHERE ExpenseID={1}", status,expenseId);
            return db.ExecuteNonQuery(CommandType.Text, sqlExpense);
        }

        /// <summary>
        /// 更新费用的‘支付状态’信息，‘未付,已付,申请,付清,月结’
        /// </summary>
        /// <param name="expenseId">费用标识</param>
        /// <param name="paystatus">被更新成为状态</param>
        /// <returns></returns>
        internal static int UpdateExpensePayStatus(string expenseId, PayApplyStatusEnum status)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sqlExpense = string.Format(@"UPDATE fee_Expense SET [PayStatus]='{0}' WHERE ExpenseID={1}", status, expenseId);
            return db.ExecuteNonQuery(CommandType.Text, sqlExpense);
        }

        /// <summary>
        /// 对指定团的所有费用状态进行审核
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <returns></returns>
        internal static int UpdateExpenseStatusAudit(string tourGroupId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sqlExpense = string.Format(@"UPDATE fee_Expense SET [Status]='{0}' WHERE TourgroupID='{1}' AND [Status]='{2}'", ExpenseStatusEnum.审核, tourGroupId, ExpenseStatusEnum.待审核);
            return db.ExecuteNonQuery(CommandType.Text, sqlExpense);
        }
        
        /// <summary>
        /// 对指定团的所有费用状态进行终结
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <returns></returns>
        internal static int UpdateExpenseStatusEnd(string tourGroupId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            string sqlExpense = string.Format(@"UPDATE fee_Expense SET [Status]='{0}' WHERE TourgroupID='{1}'AND [Status]='{2}'", ExpenseStatusEnum.终结, tourGroupId, ExpenseStatusEnum.审核);
            return db.ExecuteNonQuery(CommandType.Text, sqlExpense);
        }

        /// <summary>
        /// 对指定团的所有费用状态进行取消
        /// </summary>
        /// <param name="tourGroupId"></param>
        /// <returns></returns>
        internal static int UpdateExpenseStatusCanel(string tourGroupId)
        {
            Database db = DatabaseFactory.CreateDatabase();
            StringBuilder sql = new StringBuilder();
            sql.AppendFormat(@"UPDATE fee_Expense SET [Status]='{0}' WHERE TourgroupID='{1}';", ExpenseStatusEnum.取消, tourGroupId);
            sql.AppendFormat(@"UPDATE fee_FlightOrder SET [OrderState]='{0}' WHERE TourgroupID='{1}';", OrderStateEnum.取消, tourGroupId);
            sql.AppendFormat(@"UPDATE fee_TrainOrder SET [OrderState]='{0}' WHERE TourgroupID='{1}';", OrderStateEnum.取消, tourGroupId);
            sql.AppendFormat(@"UPDATE fee_EntertainmentOrder SET [OrderState]='{0}' WHERE TourgroupID='{1}';", OrderStateEnum.取消, tourGroupId);
            sql.AppendFormat(@"UPDATE fee_VehicleOrder SET [OrderState]='{0}' WHERE TourgroupID='{1}';", OrderStateEnum.取消, tourGroupId);
            return db.ExecuteNonQuery(CommandType.Text, sql.ToString());
        }
        /// <summary>
        /// 根据费用ID，更新的‘支付状态’为‘已付’，更新‘已支付总额’
        /// </summary>
        /// <param name="applyPaysAndExpenseIds">费用Id与已支付总额数据</param>
        /// <param name="status">状态</param>
        /// <returns></returns>
        internal static int UpdateExpensePayStatusData(Dictionary<int, decimal> applyPaysAndExpenseIds, PayApplyStatusEnum status)
        {
            Database db = DatabaseFactory.CreateDatabase();
            StringBuilder sql = new StringBuilder();
            foreach (KeyValuePair<int, decimal> item in applyPaysAndExpenseIds)
            {
                sql.AppendFormat(@"UPDATE fee_Expense SET [PayTotal]=[PayTotal]+{0},[PayStatus]='{1}' WHERE ExpenseID={2}; ", item.Value, status, item.Key);
            }

            return db.ExecuteNonQuery(CommandType.Text, sql.ToString());
        }
        /// <summary>
        /// 根据指定费用ID，批量修改费用‘支付状态’
        /// </summary>
        /// <param name="applyPaysAndExpenseIds"></param>
        /// <param name="status"></param>
        /// <returns></returns>
        internal static int UpdateExpensePayStatusData(int[] applyPaysAndExpenseIds, PayApplyStatusEnum status)
        {
            Database db = DatabaseFactory.CreateDatabase();
            StringBuilder sql = new StringBuilder();
            foreach (int id in applyPaysAndExpenseIds)
            {
                sql.AppendFormat(@"UPDATE fee_Expense SET [PayStatus]='{0}' WHERE ExpenseID={1}; ", status,id);
            }

            return db.ExecuteNonQuery(CommandType.Text, sql.ToString());
        }

        /// <summary>
        /// 更新的‘支付状态’分为‘1、未付2、已付3、付清’
        /// </summary>
        /// <param name="tourGroupId">团号</param>
        /// <param name="status">状态</param>
        /// <returns></returns>
        internal static int UpdateExpensePayStatusData(string tourGroupId, PayApplyStatusEnum status)
        {
            Database db = DatabaseFactory.CreateDatabase();
            
            string sql = string.Format(@"UPDATE fee_Expense SET [PayStatus]='{0}' WHERE TourgroupID='{1}'; ", status,tourGroupId);

            return db.ExecuteNonQuery(CommandType.Text, sql.ToString());
        }
        #endregion



    }
}

